let mysql = require("mysql2");
let config = require("../config/default.js");

let pool = mysql.createPool({
  host: config.database.HOST,
  user: config.database.USERNAME,
  password: config.database.PASSWORD,
  database: config.database.DATABASE,
});

// 查找数据
function Mysql(table, where) {
  console.log(where);
  let sql = "";
  let str = "";
  for (let k2 in where) {
    if (typeof where[k2] == "string") {
      str += k2 + "='" + where[k2] + "'";
      // return
    } else {
      //多个筛选条件使用  _WHERE+=k2+"='"+where[k2]+"' AND ";
      str += k2 + "=" + where[k2];
    }
  }
  console.log(str);
  where !== undefined
    ? (sql = "SELECT * from " + table + " where " + str)
    : (sql = "SELECT * from " + table);
  console.log(sql);
  return execute(sql);
}

// 添加数据
let addSql = (table, datas) => {
  console.log(datas);
  let fields = "";
  let values = "";
  for (let k in datas) {
    fields += k + ",";
    values = values + "'" + datas[k] + "',";
  }
  console.log(fields, values);
  fields = fields.slice(0, -1);
  values = values.slice(0, -1);
  console.log(fields, values);
  let sql = "INSERT INTO " + table + "(" + fields + ") VALUES(" + values + ")";
  return execute(sql);
};
// 修改数据
let xiuSql = function (table, sets, where) {
  let _SETS = "";
  let _WHERE = "";
  let keys = "";
  let values = "";
  for (let k in sets) {
    _SETS += k + "='" + sets[k] + "',";
  }
  _SETS = _SETS.slice(0, -1);
  for (let k2 in where) {
    //  _WHERE+=k2+"='"+where[k2]+"' AND ";
    _WHERE += k2 + "='" + where[k2] + "'";
  }
  // UPDATE user SET Password='321' WHERE UserId=12
  //update table set username='admin2',age='55'   where id="5";
  let sql = "UPDATE " + table + " SET " + _SETS + " WHERE " + _WHERE;
  console.log(sql);
  return execute(sql);
};
// 删除数据
let remSql = function (table, where) {
  let _WHERE = "";
  for (let k2 in where) {
    //多个筛选条件使用  _WHERE+=k2+"='"+where[k2]+"' AND ";
    _WHERE += k2 + "=" + where[k2];
  }
  // DELETE  FROM user WHERE UserId=12
  let sql = "DELETE  FROM " + table + " WHERE " + _WHERE;
  return execute(sql);
};
// 可以执行任何语句
function execute(sql) {
  console.log(sql);
  return new Promise((resolve, reject) => {
    pool.query(sql, (err, res) => {
      if (err) {
        reject(err);
        return;
      }
      resolve(res);
    });
  });
}
// 解义mysql返回的数
function paraphrase(res) {
  return JSON.parse(JSON.stringify(res));
}
// 封装排序方法
function compare(prop) {
  return function (a, b) {
    var value1 = a[prop];
    var value2 = b[prop];
    return value1 - value2;
  };
}
module.exports = {
  Mysql,
  addSql,
  xiuSql,
  remSql,
  execute,
  paraphrase,
  compare,
};
